{% if ( max_memory_size_mb | int ) <= (ansible_memtotal_mb | int ) %}
    {% set ansible_memtotal_mb = max_memory_size_mb %}
{% endif %}
[mysql]
auto-rehash
socket                              =/tmp/mysql-{{mysql_port}}.sock             #   /tmp/mysql.sock


[mysqld]
####: for global
user                                ={{mysql_user}}                          #	mysql
basedir                             ={{mysql_base_dir}}              #	/usr/local/mysql/
datadir                             ={{mysql_data_dir}}     #	/usr/local/mysql/data
server_id                           =1312                           #	0
port                                ={{mysql_port}}                           #	3306
character_set_server                =utf8                           #	latin1
log_bin_trust_function_creators     =ON                             #   0
max_prepared_stmt_count             =1048576                        #   
log_timestamps                      =system                         #	utc
socket                              =/tmp/mysql-{{mysql_port}}.sock                #	/tmp/mysql.sock
read_only                           =OFF                            #	off
skip_name_resolve                   =1                              #   0
auto_increment_increment            =1                              #	1
auto_increment_offset               =1                              #	1
lower_case_table_names              =1                              #	0
secure_file_priv                    =                               #	null
open_files_limit                    =102000                         #   1024
{% if (ansible_memtotal_mb | int)   <= 4096 %}
thread_cache_size                   =16                             #   9
max_connections                     =151                            # 151
{% elif (ansible_memtotal_mb | int) <= 8192 %}
thread_cache_size                   =32                             #   9
max_connections                     =255                            # 151
{% elif (ansible_memtotal_mb | int) <= 16384 %}
thread_cache_size                   =64                             #   9
max_connections                     =512                            # 151
{% elif (ansible_memtotal_mb | int) <= 32768 %}
thread_cache_size                   =128                            #   9
max_connections                     =1000                           # 151
{% else %}
thread_cache_size                   =128                            #   9
max_connections                     =1024                           # 151
{% endif %}


####: for table cache
{% if (ansible_memtotal_mb | int)   <= 4096 %}
table_open_cache                    =4000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =16                             #   16
{% elif (ansible_memtotal_mb | int) <= 8192 %}
table_open_cache                    =8000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =16                             #   16
{% elif (ansible_memtotal_mb | int) <= 16384 %}
table_open_cache                    =16000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
{% elif (ansible_memtotal_mb | int) <= 32768 %}
table_open_cache                    =20000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
{% else %}
table_open_cache                    =24000                           #   2000
table_definition_cache              =2000                           #   1400
table_open_cache_instances          =32                             #   16
{% endif %}

####: for binlog
binlog_format                       =ROW                            #	row
log_bin                             =mysql-bin                      #	off
binlog_rows_query_log_events        =ON                             #	off
log_slave_updates                   =ON                             #	off
expire_logs_days                    =7                              #	0
binlog_cache_size                   =64k                            #	65536(64k)
binlog_checksum                     =none                           #	CRC32
sync_binlog                         =1                              #	1
slave-preserve-commit-order         =ON                             #    

####: for error-log
log_error                           =err.log                        #	/usr/local/mysql/data/localhost.localdomain.err

####: for general-log
general_log                         =OFF                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =ON                             #    off
slow_query_log_file                 =slow.log                       #    hostname.log
log_queries_not_using_indexes       =OFF                            #    off
long_query_time                     =2.0                            #    10.000000

####: for gtid
gtid_executed_compression_period    =1000                           #	1000
gtid_mode                           =ON                             #	off
enforce_gtid_consistency            =ON                             #	off


####: for replication
skip_slave_start                    =0                              #	
master_info_repository              =table                          #	file
relay_log_info_repository           =table                          #	file
slave_parallel_type                 =logical_clock                  #    database | LOGICAL_CLOCK
slave_parallel_workers              ={{[ansible_processor_count * 4 ,16] | min}}                              #    0
rpl_semi_sync_master_enabled        =1                              #    0
rpl_semi_sync_slave_enabled         =1                              #    0
rpl_semi_sync_master_timeout        =1000                           #    1000(1 second)
plugin_load_add                     =semisync_master.so             #
plugin_load_add                     =semisync_slave.so              #
binlog_group_commit_sync_delay      =200                            #    0      200(0.02% seconde) 
binlog_group_commit_sync_no_delay_count = 10                        #    0
binlog_transaction_dependency_tracking  = WRITESET                  #    COMMIT_ORDER | WRITESET       
transaction_write_set_extraction        = XXHASH64


####: for innodb
default_storage_engine                          =innodb                     #	innodb
default_tmp_storage_engine                      =innodb                     #	innodb
innodb_data_file_path                           =ibdata1:256M:autoextend    #	ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:64M:autoextend      #	ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #	ib_buffer_pool
innodb_log_group_home_dir                       =./                         #	./
innodb_log_files_in_group                       =8                          #	2
innodb_log_file_size                            =128M                       #	50331648(48M)
innodb_file_per_table                           =ON                         #	on
innodb_online_alter_log_max_size                =128M                       #   134217728(128M)
innodb_open_files                               =64000                      #   2000
innodb_page_size                                =16k                        #	16384(16k)
innodb_thread_concurrency                       =0                          #	0
innodb_read_io_threads                          =4                          #	4
innodb_write_io_threads                         =4                          #	4
innodb_purge_threads                            =4                          #	4(garbage collection)
innodb_page_cleaners                            =4                          #   4(flush lru list)
innodb_print_all_deadlocks                      =ON                         #	off
innodb_deadlock_detect                          =ON                         #	on
innodb_lock_wait_timeout                        =50                         #	50
innodb_spin_wait_delay                          =6                          #	6
innodb_autoinc_lock_mode                        =2                          #	1
innodb_flush_sync                               =OFF                        #   on
innodb_io_capacity                              =4000                       #   200
innodb_io_capacity_max                          =20000                      #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =ON                         #   on
innodb_stats_persistent                         =ON                         #	on
innodb_stats_persistent_sample_pages            =20                         #	20
innodb_adaptive_hash_index                      =ON                         #	on
innodb_change_buffering                         =all                        #	all
innodb_change_buffer_max_size                   =25                         #	25
innodb_flush_neighbors                          =0                          #	1
innodb_flush_method                             =O_DIRECT                   #	
innodb_doublewrite                              =ON                         #	on
innodb_log_buffer_size                          =64M                       #	16777216(16M)
innodb_flush_log_at_timeout                     =1                          #	1
innodb_flush_log_at_trx_commit                  =1                          #	1
{% if (ansible_memtotal_mb | int)     <= 512 %}
innodb_buffer_pool_size             =128M                             # 134217728(128M)
innodb_buffer_pool_instances        =1                                # 1
{% elif (ansible_memtotal_mb | int)   <= 1024 %}
innodb_buffer_pool_size             =256M                             # 134217728(128M)
innodb_buffer_pool_instances        =1                                # 1
{% elif (ansible_memtotal_mb | int)   <= 4096 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.5 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =1                                # 1
{% elif (ansible_memtotal_mb | int)   <= 8109 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.6 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =2                                # 1
{% elif (ansible_memtotal_mb | int)   <= 16384 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.65 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =4                                # 1
{% elif (ansible_memtotal_mb | int)   <= 32768 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.70 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =8                                # 1
{% elif (ansible_memtotal_mb | int)   <= 65536 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.75 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =16                                # 1
{% elif (ansible_memtotal_mb | int)   <= 131072 %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.80 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =24                                # 1
{% else %}
innodb_buffer_pool_size             ={{ (ansible_memtotal_mb * 0.85 // 128 ) | int * 128 }}M                            # 134217728(128M)
innodb_buffer_pool_instances        =32                                # 1
{% endif %}
autocommit                                      =ON                          #	1
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =off                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =50                         #    25 
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON


####  for performance_schema
performance_schema                                                      =on    #    on
performance_schema_consumer_global_instrumentation                      =on    #    on
performance_schema_consumer_thread_instrumentation                      =on    #    on
performance_schema_consumer_events_stages_current                       =on    #    off
performance_schema_consumer_events_stages_history                       =on    #    off
performance_schema_consumer_events_stages_history_long                  =off   #    off
performance_schema_consumer_statements_digest                           =on    #    on
performance_schema_consumer_events_statements_current                   =on    #    on
performance_schema_consumer_events_statements_history                   =on    #    on
performance_schema_consumer_events_statements_history_long              =off   #    off
performance_schema_consumer_events_waits_current                        =on    #    off
performance_schema_consumer_events_waits_history                        =on    #    off
performance_schema_consumer_events_waits_history_long                   =off   #    off
performance-schema-instrument                                           ='memory/%=COUNTED'


# -- ~ _ ~    ~ _ ~     ~ _ ~ -- 
# base on mysql-5.7.24 
# generated by https://www.sqlpy.com 2019年5月22日 15:39
# wechat: jianglegege
# email: 1721900707@qq.com
# -- ~ _ ~ -- 
